package com.wutong.eams.mapper;

import com.wutong.eams.entity.Student;
import com.wutong.eams.entity.StudentInfo;
import com.wutong.eams.other.ConditionBuilder;
import com.wutong.eams.other.Page;
import com.wutong.eams.utils.DatabaseUtil;

import java.util.List;

public interface StudentMapper extends BaseMapper<Student> {

    @Override
    default Class<Student> getEntityClass() {
        return Student.class;
    }

    @Override
    default String getTableName() {
        return "students";
    }

    default List<StudentInfo> getAllStudentInfos() {
        String sql = "SELECT s.student_id, s.user_id, s.student_no, c.class_name, " +
                "s.admission_date, u.gender, u.real_name " +
                "FROM students s " +
                "JOIN classes c ON s.class_id = c.class_id " +
                "JOIN users u ON s.user_id = u.user_id";
        return DatabaseUtil.executeQuery(sql, StudentInfo.class);
    }

    default Page<StudentInfo> getAllStudentInfosByPage(int pageNum, int pageSize) {
        String baseSql = "SELECT s.student_id, s.user_id, s.student_no, c.class_name, " +
                "s.admission_date, u.gender, u.real_name " +
                "FROM students s " +
                "JOIN classes c ON s.class_id = c.class_id " +
                "JOIN users u ON s.user_id = u.user_id ";

        String querySql = baseSql + "LIMIT " + (pageNum - 1) * pageSize + ", " + pageSize;
        String countSql = "SELECT COUNT(*) FROM students";

        List<StudentInfo> result = DatabaseUtil.executeQuery(querySql, StudentInfo.class);
        int total = DatabaseUtil.getCountBySql(countSql);
        return new Page<>(pageNum, pageSize, total, result);
    }

    default Page<StudentInfo> getStudentInfosByCondition(Integer classId, String studentNo, String keyword, int pageNum, int pageSize) {
        ConditionBuilder builder = new ConditionBuilder().where("1", ConditionBuilder.Operator.EQUAL, 1);

        if (classId != null) {
            builder.and("s.class_id", ConditionBuilder.Operator.EQUAL, classId);
        }
        if (studentNo != null) {
            builder.and("s.student_no", ConditionBuilder.Operator.LIKE, "%" + studentNo + "%");
        }
        if (keyword != null) {
            ConditionBuilder group = new ConditionBuilder()
                    .where("u.real_name", ConditionBuilder.Operator.LIKE, "%" + keyword + "%")
                    .or("s.student_no", ConditionBuilder.Operator.LIKE, "%" + keyword + "%");
            builder.andGroup(group);
        }

        String baseSql = "SELECT s.student_id, s.user_id, s.student_no, c.class_name, " +
                "s.admission_date, u.gender, u.real_name " +
                "FROM students s " +
                "JOIN classes c ON s.class_id = c.class_id " +
                "JOIN users u ON s.user_id = u.user_id ";

        String countSql = "SELECT COUNT(*) FROM students s " +
                "JOIN users u ON s.user_id = u.user_id " +
                builder.build();

        String querySql = baseSql + builder.build() +
                " LIMIT " + (pageNum - 1) * pageSize + ", " + pageSize;

        List<StudentInfo> result = DatabaseUtil.executeQuery(querySql, StudentInfo.class);
        int total = DatabaseUtil.getCountBySql(countSql);
        return new Page<>(pageNum, pageSize, total, result);
    }
}
